Alter FUNCTION FN_LOAD_ALL_FAB_TRANSIT_LOTS( @PROCESS_STEP_IDS INT, @date datetime, @next_location varchar(50) )

RETURNS  @FN_RESULT TABLE( LOT_NUMBER VARCHAR(50),PART_NUMBER VARCHAR(50),PO_NUMBER VARCHAR(50),SO_NUMBER VARCHAR(50),INVOICE VARCHAR(50),PROCESS_TYPE_NAME VARCHAR(50),
				PREVIOUS_LOC VARCHAR(50),NEXT_LOC VARCHAR(50),SHIP_DATE DATETIME, SHIP_QTY INT, PS_TYPE_ID INT, SHIPPEDLOT_ID INT,WAFER_ID VARCHAR(100) )
AS
BEGIN


	DECLARE @LOT_NUMBER 	VARCHAR(50)
	DECLARE @PART_NUMBER 	VARCHAR(50)
	DECLARE @PO_NUMBER 	VARCHAR(50)
	DECLARE @SO_NUMBER 	VARCHAR(50)
	DECLARE @INVOICE 	VARCHAR(50)
	DECLARE @PREVIOUS_LOC 	VARCHAR(50)
	DECLARE @NEXT_LOC 	VARCHAR(50)
	DECLARE @WAFER_ID	VARCHAR(100)
	DECLARE @SHIP_DATE 	DATETIME 
	DECLARE @SHIP_QTY 	INT
	DECLARE @PROCESS_TYPE_NAME VARCHAR(50)
	DECLARE @PS_TYPE_ID	INT 	
	DECLARE @PROCESS_STEPS_TABLE TABLE( PROCESSSTEP_ID INT )
	--DEclare @date datetime

	

	
	--inserting the process step Id's passes as parameter into this table	
	INSERT INTO @PROCESS_STEPS_TABLE( PROCESSSTEP_ID )  SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PROCESS_STEP_IDS )

	DECLARE @IS_HOLD BIT
	DECLARE @NO_OF_DAYS INT
	DECLARE @SHIPPEDLOT_ID INT
	DECLARE @SHIP_JIT_WINDOW INT
		
	DECLARE @RESULTS TABLE (LOT_NUMBER VARCHAR(50),PART_NUMBER VARCHAR(50),PO_NUMBER VARCHAR(50),SO_NUMBER VARCHAR(50),INVOICE VARCHAR(50),PROCESS_TYPE_NAME VARCHAR(50),
				PREVIOUS_LOC VARCHAR(50),NEXT_LOC VARCHAR(50),SHIP_DATE DATETIME, SHIP_QTY INT, PS_TYPE_ID INT, SHIPPEDLOT_ID INT,WAFER_ID VARCHAR(100) )

	DECLARE @TRANSITCURSOR CURSOR
	

		SET @TRANSITCURSOR = CURSOR FOR SELECT LT.LOT_NUMBER , PRO.PART_NUMBER , TLT.TLT_CUSTOMER_PO , TLT.TLT_CUSTOMER_SO,
	TLT.TLT_INVOICE_NUMBER , PS.PS_TYPE_NAME, VEN.V_NAME , TLT.TLT_NEXTLOCATION, SL.SHIP_DATE, SL.SHIP_QTY, SL.SHIPPEDLOT_ID ,TLT.WAFER_ID
	FROM LOT_TABLE LT, SHIPPED_LOT SL, TRANSIT_LOT_TABLE TLT, PRODUCT PRO, PROCESS_STEP PS, LOCATION LOC, VENDOR VEN
	WHERE SL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND  SL.SHIPPEDLOT_ID = TLT.SHIPPEDLOT_ID AND SL.STATUS = 'TRANSIT' AND TLT.TLT_NEXTLOCATION = @NEXT_LOCATION AND
	LT.LOCATION_ID = LOC.LOCATION_ID AND LOC.VENDOR_ID = VEN.VENDOR_ID AND SL.SHIP_STATUS <= 0

	OPEN @TRANSITCURSOR
	FETCH NEXT FROM @TRANSITCURSOR INTO @LOT_NUMBER, @PART_NUMBER , @PO_NUMBER , @SO_NUMBER,  @INVOICE , @PROCESS_TYPE_NAME, @PREVIOUS_LOC , @NEXT_LOC , @SHIP_DATE, @SHIP_QTY, @SHIPPEDLOT_ID ,@WAFER_ID  
	
	--Select getDate()


	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @no_of_days =  DATEDIFF(day, @Ship_Date, @date ) 
		SELECT @ship_jit_window = Ship_JIT_WINDOW from Client_Table
		
		if @no_of_days >= @ship_jit_window
		begin

			INSERT INTO @RESULTS(LOT_NUMBER,PART_NUMBER,PO_NUMBER,SO_NUMBER,INVOICE,PROCESS_TYPE_NAME,PREVIOUS_LOC,NEXT_LOC,SHIP_DATE,SHIP_QTY, PS_TYPE_ID, SHIPPEDLOT_ID,WAFER_ID) 
			VALUES (@LOT_NUMBER,@PART_NUMBER,@PO_NUMBER,@SO_NUMBER,@INVOICE,@PROCESS_TYPE_NAME,@PREVIOUS_LOC,@NEXT_LOC,@SHIP_DATE,@SHIP_QTY, @PS_TYPE_ID, @SHIPPEDLOT_ID,@WAFER_ID ) 
		
		end

		FETCH NEXT FROM @TRANSITCURSOR INTO @LOT_NUMBER, @PART_NUMBER , @PO_NUMBER , @SO_NUMBER, @INVOICE , @PROCESS_TYPE_NAME, @PREVIOUS_LOC , @NEXT_LOC , @SHIP_DATE, @SHIP_QTY, @SHIPPEDLOT_ID,@WAFER_ID
		
	END
	CLOSE @TRANSITCURSOR
	DEALLOCATE @TRANSITCURSOR
	
	INSERT @FN_RESULT SELECT * FROM @RESULTS ORDER BY PS_TYPE_ID, SHIP_DATE,PART_NUMBER DESC  		
	
RETURN
END

